# coding: utf-8

# 重启七月拉新任务
# 需要将机器人已经打过电话的拉新用户忽略

import set_env_path

import os

import xlrd
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def main():
    book = xlrd.open_workbook('./update_user_marketing_info_july.xlsx')
    sheet = book.sheet_by_index(0)

    # 因为user_marketing_info_july的mobile没有索引，所以先找user_id
    find_user_id_sql = text("""
        select id from user where mobile=:mobile;
    """)

    # 将已经机器人打过电话的客户设置为30，临时过渡
    update_maked_phonecall_sql = text("""
        update user_marketing_info_july set status=30 where user_id=:user_id;
    """)

    # 将没打过电话的客户设置为10，正在营销
    update_sql1 = text("""
        update user_marketing_info_july set status=10 where status=20;
    """)

    # 将过渡的客户设置为20，停止营销
    update_sql2 = text("""
        update user_marketing_info_july set status=20 where status=30;
    """)

    for row in range(1, sheet.nrows):
        mobile = str(sheet.cell(row, 0).value)
        tmp_user = conn.execute(
            find_user_id_sql,
            mobile=mobile
        ).fetchone()
        if tmp_user:
            conn.execute(
                update_maked_phonecall_sql,
                user_id=tmp_user.id
            )

    conn.execute(update_sql1)
    conn.execute(update_sql2)

if __name__ == '__main__':
    main()
